﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
//using System.Windows.Forms;

namespace NhaSachDAO
{
    public class Sach
    {
        public static DataTable LoadDanhSachSach()
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cnd = new SqlCommand("select * from Sach", cnn);
            cnd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cnd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable LoadDanhSachSachma(string masach)
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cnd = new SqlCommand("select * from Sach where MaSach like '"+masach+"'", cnn);
            cnd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cnd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable LoadDanhSachSachTheoten(string ts)
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cnd = new SqlCommand("select * from Sach Where TenSach like '%"+ts+"%'", cnn);
            cnd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cnd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable LoadDanhSachSachTheotacgia(string tg)
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cnd = new SqlCommand("select * from Sach Where TacGia like'%"+tg+"%'", cnn);
            cnd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cnd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable LoadDanhSachSachTheoTheLoai(string ml)
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cnd = new SqlCommand("select * from Sach Where MaLoai like'%" + ml + "%'", cnn);
            cnd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cnd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static void XoaSach(int masach)
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cmd = new SqlCommand("DELETE FROM Sach WHERE (MaSach = " + masach + ")", cnn);
            cmd.CommandType = CommandType.Text;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static void ThemSach(Int64 masach,string maloai, string tensach,string tacgia,int soluong,string thuocnxb,string donvitinh,int dongia,int soluongton)
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cmd = new SqlCommand("Insert into Sach(MaSach,MaLoai,TenSach,TacGia,SoLuong,ThuocNXB,DonViTinh,DonGia,SoLuongTon) Values ( "+masach+","+maloai+",N'"+tensach+"',N'"+tacgia+"',"+soluong+",N'"+thuocnxb+"',N'"+donvitinh+"',"+dongia+","+soluongton+")", cnn);
            cmd.CommandType = CommandType.Text;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static void CapNhatSach(Int64 masach, string maloai, string tensach, string tacgia, int soluong, string thuocnxb, string donvitinh, int dongia, int soluongton)
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cmd = new SqlCommand("Update Sach set MaSach=" +masach+ ",MaLoai=N'" +maloai+ "',TenSach=N'" +tensach+ "',TacGia=N'" +tacgia+ "',SoLuong=" + soluong +",ThuocNXB=N'"+ thuocnxb +"',DonViTinh=N'" +donvitinh+ "',DonGia=" +dongia+ ",SoLuongTon=" +soluongton+ " Where (MaSach=" +masach+ ")", cnn);
            cmd.CommandType = CommandType.Text;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }


    }
}
